/*
 * Created on Sep 10, 2003
 * UserGroupDB
 * Local Projects Database
 * (c) 2003 eRomania Gateway Association
 *
 */
package ro.gateway.aida.usr;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Comparator;
import java.util.Iterator;
import java.util.Set;
import java.util.TreeSet;
import java.util.Vector;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;



/**
 * @author Mihai Postelnicu
 * UserGroupDB
 * Defines accessors for managing user groups.
 *
 */
public class UserGroupDB extends DBPersistenceManager
{
      private UserGroupDB( PersistenceToken token )
      {
            super( token );
      }

      public static UserGroupDB getManager( PersistenceToken token )
      {
            return new UserGroupDB( token );
      }

      public boolean addSuperGroup( int ugid, int sgid ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT_RELATION );
                  ps.setInt( 1, ugid );
                  ps.setInt( 2, sgid );
                  ps.executeUpdate();

                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return true;
      }

      public Set getSubGroups( int gid ) throws SQLException
      {
            Connection con = getConnection();
            TreeSet ret = new TreeSet( new UserGroup.UserGroupComparator() );

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_SUBGROUPS );
                  ps.setInt( 1, gid );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        UserGroup ug = new UserGroup();
                        ug.setId( rs.getInt( 1 ) );
                        ug.setName( rs.getString( 2 ) );
                        ug.setDescription( rs.getString( 3 ) );
                        ug.setRole( GroupRole.createRole( rs.getInt( 4 ) ) );
                        ret.add( ug );
                  }

                  rs.close();
                  ps.close();

                  return ret;
            }
            finally
            {
                  con.close();
            }
      }

      public Vector getUsers( int gid ) throws SQLException
      {
            Connection con = getConnection();
            UserDB udb = UserDB.getManager( token );
            Vector ret = new Vector();

            try
            {
                  PreparedStatement ps = con.prepareStatement( RETRIEVE_GROUP_USERS );
                  ps.setInt( 1, gid );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        User item = new User();
                        item.setId( rs.getLong( 1 ) );
                        item.setLogin( rs.getString( 2 ) );
                        item.setPass( rs.getString( 3 ) );
                        item.setFirstNames( rs.getString( 4 ) );
                        item.setLastName( rs.getString( 5 ) );
                        item.setEmail( rs.getString( 6 ) );
                        item.setWorkingLanguage( rs.getString( 7 ) );
                        item.setStandby( rs.getInt( 8 ) == 1 );
                        item.setOrganization( rs.getString( 9 ) );
                        ret.add( item );
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      public int getUserCount( int gid ) throws SQLException
      {
            Connection con = getConnection();
            UserDB udb = UserDB.getManager( token );           
            int count=0;
            try
            {
                  PreparedStatement ps = con.prepareStatement( COUNT_GROUP_USERS );
                  ps.setInt( 1, gid );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        count=rs.getInt(1);
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return count;
      }

      
      public boolean addUser( int gid, long uid ) throws SQLException
      {
            Connection con = getConnection();

            //fist ensure user is unlinked from all other groups:
            unlinkUserFromAll( uid );

            try
            {
                  PreparedStatement ps = con.prepareStatement( ADD_GROUP_USER );
                  ps.setInt( 1, gid );
                  ps.setLong( 2, uid );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException e )
            {
                  return false;
            }
            finally
            {
                  con.close();
            }

            return true;
      }

      public void unlinkUser( int gid, long uid ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DEL_GROUP_USER );
                  ps.setInt( 1, gid );
                  ps.setLong( 2, uid );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public void unlinkUserFromAll( long uid ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( UNLINK_USER_FROM_ALL );
                  ps.setLong( 1, uid );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public void unlinkSuperGroup( int ug, int sg ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_RELATION );
                  ps.setInt( 1, ug );
                  ps.setInt( 2, sg );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public int addGroup( UserGroup ug ) throws SQLException
      {
            Connection con = getConnection();
            int ret = 0;

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT_GROUP );
                  ps.setString( 1, ug.getName() );
                  ps.setString( 2, ug.getDescription() );
                  ps.setInt( 3, ug.getRole().getId() );
                  ps.executeUpdate();
                  ps.close();

                  ps = con.prepareStatement( GET_MAX_GROUP );

                  ResultSet rs = ps.executeQuery();

                  if ( rs.next() )
                        ret = rs.getInt( 1 );

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      public void updateGroup( UserGroup ug ) throws SQLException
      {
            Connection con = getConnection();

            //if group role is admin, we don't have supergroups (WB requirement), wipe'em all!:
            if ( ug.getRole().getId() == GroupRole.ADMINISTRATOR_ROLE )
                  wipeGroupSuperGroups( ug.getId() );

            try
            {
                  PreparedStatement ps = con.prepareStatement( UPDATE_GROUP );
                  ps.setString( 1, ug.getName() );
                  ps.setString( 2, ug.getDescription() );
                  ps.setInt( 3, ug.getRole().getId() );
                  ps.setInt( 4, ug.getId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public UserGroup getById( int id ) throws SQLException
      {
            if ( id == 0 )
            {
                  UserGroup g = new UserGroup();
                  g.setId( 0 );
                  g.setName( "[Unassigned]" );

                  return g;
            }

            Connection con = getConnection();
            UserGroup ret = new UserGroup();

            try
            {
                  PreparedStatement ps = con.prepareStatement( RETRIEVE_GROUP );
                  ps.setInt( 1, id );

                  ResultSet rs = ps.executeQuery();

                  if ( rs.next() )
                  {
                        ret.setName( rs.getString( 1 ) );
                        ret.setDescription( rs.getString( 2 ) );
                        ret.setRole( GroupRole.createRole( rs.getInt( 3 ) ) );
                  }

                  ps.close();
                  rs.close();

                  ps = con.prepareStatement( GET_GROUP_SUPERGROUPS );
                  ps.setInt( 1, id );
                  rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        UserGroup ug = new UserGroup();
                        ug.setId( rs.getInt( 1 ) );
                        ug.setName( rs.getString( 2 ) );
                        ug.setDescription( rs.getString( 3 ) );
                        ug.setRole( GroupRole.createRole( rs.getInt( 4 ) ) );
                        ret.getSuperGroups().add( ug );
                  }

                  ps.close();
                  rs.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      public void wipeGroupSuperGroups( int gid ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( WIPE_GROUP_SUPERGROUPS );
                  ps.setInt( 1, gid );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public void wipeGroup( int id ) throws SQLException
      {
            wipeGroupSuperGroups( id );
            wipeGroupUsers( id );
      }

      public void wipeGroupUsers( int id ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( WIPE_GROUP_USERS );
                  ps.setInt( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      /*
       * You cannot delete a group unless there is no person assinged to that
       * group
       */
      public boolean deleteGroup( int id ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( VERIFY_ASSINGED );
                  ps.setInt( 1, id );

                  ResultSet rs = ps.executeQuery();

                  if ( rs.next() && ( rs.getInt( 1 ) != 0 ) )
                  {
                        ps.close();
                        rs.close();

                        return false;
                  }

                  ps.close();
                  rs.close();
                  ps = con.prepareStatement( DELETE_GROUP );
                  ps.setInt( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return true;
      }

      public Set createGroupList() throws SQLException
      {
            Connection con = getConnection();
            Comparator c = new Comparator()
            {
                  public int compare( Object o1, Object o2 )
                  {
                        UserGroup ug1 = ( UserGroup ) o1;
                        UserGroup ug2 = ( UserGroup ) o2;

                        return ( ug1.getName().compareTo( ug2.getName() ) );
                  }
            };

            TreeSet returned = new TreeSet( c );

            try
            {
                  PreparedStatement ps = con.prepareStatement( RETRIEVE_GROUPS );
                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        UserGroup ug = new UserGroup();
                        ug.setId( rs.getInt( 1 ) );
                        ug.setName( rs.getString( 2 ) );
                        ug.setDescription( rs.getString( 3 ) );
                        ug.setRole( GroupRole.createRole( rs.getInt( 4 ) ) );
                        returned.add( ug );
                  }

                  ps.close();
                  rs.close();

                  //create interlinking between groups and supergroups
                  ps = con.prepareStatement( GET_GROUPS_SUPERGROUPS );
                  rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        int k = rs.getInt( 1 );
                        UserGroup ug = searchForGroup( returned, k );
                        int x = rs.getInt( 2 );
                        UserGroup sg = searchForGroup( returned, x );
                        if(ug!=null && sg!=null) ug.getSuperGroups().add( sg );
                  }

                  ps.close();
                  rs.close();
            }
            finally
            {
                  con.close();
            }

            return returned;
      }

      public static UserGroup searchForGroup( TreeSet gl, int gid )
      {
            Iterator i = gl.iterator();

            while ( i.hasNext() )
            {
                  UserGroup element = ( UserGroup ) i.next();

                  if ( element.getId() == gid )
                        return element;
            }

            return null;
      }

      public UserGroup getGroupForUser( long user_id ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_GROUPS_ID_FOR_USER );
                  ps.setLong( 1, user_id );

                  ResultSet rs = ps.executeQuery();
                  UserGroup ug = null;

                  if ( rs.next() )
                        ug = getById( rs.getInt( 1 ) );

                  rs.close();
                  ps.close();

                  return ug;
            }
            finally
            {
                  con.close();
            }
      }

      private static final String COUNT_GROUP_USERS =
        "SELECT count(*) "+
        "FROM users u, users_groups ug WHERE ug.user_id=u.id AND ug.group_id=?";
      
      private static final String RETRIEVE_GROUP_USERS =
            "SELECT u.id, u.login, u.pass, u.first_names, u.last_name, u.email, u.wlang, " +
            "u.standby, u.organization " +
            "FROM users u, users_groups ug WHERE ug.user_id=u.id AND ug.group_id=?" +
            "ORDER BY u.last_name, u.first_names";
      private static final String GET_SUBGROUPS =
            "SELECT g.id,g.name,g.description,g.role FROM" +
            " group_relations gr, groups g where gr.id=g.id and gr.supergroup=?";
      private static final String UNLINK_USER_FROM_ALL = "DELETE FROM users_groups WHERE user_id=?";
      private static final String GET_GROUPS_ID_FOR_USER = "SELECT group_id from users_groups WHERE user_id=?";
      private static final String WIPE_GROUP_USERS = "DELETE FROM users_groups WHERE group_id=?";
      private static final String WIPE_GROUP_SUPERGROUPS = "DELETE FROM group_relations WHERE id=?";
      private static final String GET_MAX_GROUP = "SELECT MAX(id) FROM groups";
      private static final String ADD_GROUP_USER = "INSERT INTO users_groups (group_id,user_id) VALUES (?,?)";
      private static final String DEL_GROUP_USER = "DELETE FROM users_groups WHERE group_id=? AND user_id=?";
      private static final String RETRIEVE_GROUP = "SELECT name,description,role FROM groups WHERE id=?";
      private static final String UPDATE_GROUP =
            "UPDATE groups SET name=?,description=?,role=? " + "WHERE id=?";
      private static final String RETRIEVE_GROUPS =
            "SELECT gt.id,gt.name,gt.description,gt.role" + " FROM groups gt";
      private static final String GET_GROUPS_SUPERGROUPS = "SELECT id,supergroup FROM group_relations";
      private static final String GET_GROUP_SUPERGROUPS =
            "SELECT g.id,g.name,g.description,g.role FROM" +
            " group_relations gr, groups g where gr.supergroup=g.id and gr.id=?";
      private static final String INSERT_GROUP =
            "INSERT INTO groups (name,description,role) " + "VALUES (?,?,?)";
      private static final String VERIFY_ASSINGED = "SELECT count(*) FROM users_groups where group_id=?";
      private static final String DELETE_GROUP = "DELETE FROM groups WHERE id=?";
      private static final String VERIFY_RELATION =
            "SELECT count(*) FROM group_relations WHERE " +
            "id=? AND supergroup=?";
      private static final String INSERT_RELATION =
            "INSERT INTO group_relations (id,supergroup) VALUES " + "(?,?)";
      private static final String DELETE_RELATION = "DELETE FROM group_relations WHERE id=? AND supergroup=?";
}
